package flights.db;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.StringReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.smartcardio.CommandAPDU;

public class DBInitialize 
{
	private static String FLIGHTS_DB = "/*\r\n" + 
			"SQLyog Community v11.2 (32 bit)\r\n" + 
			"MySQL - 5.5.32 : Database - flightsrus\r\n" + 
			"*********************************************************************\r\n" + 
			"*/\r\n" + 
			"\r\n" + 
			"\r\n" + 
			"/*!40101 SET NAMES utf8 */;\r\n" + 
			"\r\n" + 
			"/*!40101 SET SQL_MODE=''*/;\r\n" + 
			"\r\n" + 
			"/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;\r\n" + 
			"/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;\r\n" + 
			"/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;\r\n" + 
			"/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;\r\n" + 
			"CREATE DATABASE /*!32312 IF NOT EXISTS*/`flightsrus` /*!40100 DEFAULT CHARACTER SET latin1 */;\r\n" + 
			"\r\n" + 
			"USE `flightsrus`;\r\n" + 
			"\r\n" + 
			"/*Table structure for table `airline` */\r\n" + 
			"\r\n" + 
			"DROP TABLE IF EXISTS `airline`;\r\n" + 
			"\r\n" + 
			"CREATE TABLE `airline` (\r\n" + 
			"  `airline_id` int(11) NOT NULL,\r\n" + 
			"  `logon` varchar(50) NOT NULL,\r\n" + 
			"  `password` varchar(50) NOT NULL,\r\n" + 
			"  `name` varchar(50) NOT NULL,\r\n" + 
			"  `registration_date` date NOT NULL,\r\n" + 
			"  PRIMARY KEY (`airline_id`)\r\n" + 
			") ENGINE=InnoDB DEFAULT CHARSET=latin1;\r\n" + 
			"\r\n" + 
			"/*Data for the table `airline` */\r\n" + 
			"\r\n" + 
			"insert  into `airline`(`airline_id`,`logon`,`password`,`name`,`registration_date`) values (1,'hotair','hotair111','Hot Air','2013-08-05'),(2,'fighterflight','fight123','Fighter Flight','2013-08-11'),(3,'clearsky','clear111','Clear Sky','2013-07-22');\r\n" + 
			"\r\n" + 
			"/*Table structure for table `booking` */\r\n" + 
			"\r\n" + 
			"DROP TABLE IF EXISTS `booking`;\r\n" + 
			"\r\n" + 
			"CREATE TABLE `booking` (\r\n" + 
			"  `booking_id` int(11) NOT NULL,\r\n" + 
			"  `user_id` int(11) NOT NULL,\r\n" + 
			"  `payment_method_id` int(11) NOT NULL,\r\n" + 
			"  PRIMARY KEY (`booking_id`),\r\n" + 
			"  KEY `flt_bk_mtd` (`payment_method_id`),\r\n" + 
			"  KEY `flt_bk_usr` (`user_id`),\r\n" + 
			"  CONSTRAINT `flt_bk_mtd` FOREIGN KEY (`payment_method_id`) REFERENCES `payment_method` (`method_id`),\r\n" + 
			"  CONSTRAINT `flt_bk_usr` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)\r\n" + 
			") ENGINE=InnoDB DEFAULT CHARSET=latin1;\r\n" + 
			"\r\n" + 
			"/*Data for the table `booking` */\r\n" + 
			"\r\n" + 
			"insert  into `booking`(`booking_id`,`user_id`,`payment_method_id`) values (1,1,2),(2,2,3),(3,3,4);\r\n" + 
			"\r\n" + 
			"/*Table structure for table `flight` */\r\n" + 
			"\r\n" + 
			"DROP TABLE IF EXISTS `flight`;\r\n" + 
			"\r\n" + 
			"CREATE TABLE `flight` (\r\n" + 
			"  `flight_id` int(11) NOT NULL,\r\n" + 
			"  `airline_id` int(11) NOT NULL,\r\n" + 
			"  `flight_number` varchar(10) NOT NULL,\r\n" + 
			"  `depart_airport` varchar(50) NOT NULL,\r\n" + 
			"  `arrival_airport` varchar(50) NOT NULL,\r\n" + 
			"  `depart_date` date NOT NULL,\r\n" + 
			"  `arrive_date` date NOT NULL,\r\n" + 
			"  PRIMARY KEY (`flight_id`),\r\n" + 
			"  KEY `flt_flt_arln` (`airline_id`),\r\n" + 
			"  CONSTRAINT `flt_flt_arln` FOREIGN KEY (`airline_id`) REFERENCES `airline` (`airline_id`)\r\n" + 
			") ENGINE=InnoDB DEFAULT CHARSET=latin1;\r\n" + 
			"\r\n" + 
			"/*Data for the table `flight` */\r\n" + 
			"\r\n" + 
			"insert  into `flight`(`flight_id`,`airline_id`,`flight_number`,`depart_airport`,`arrival_airport`,`depart_date`,`arrive_date`) values (1,1,'BCN123','Ben Gurion','Barcelona','2013-08-04','2013-08-05'),(2,2,'BER321','Ben Gurion','Berlin','2013-08-11','2013-08-12'),(3,1,'BCN111','Ben Gurion','Barcelona','2013-08-18','2013-08-19'),(4,3,'AMS681','Ben Gurion','Amsterdam','2013-08-26','2013-08-27'),(5,3,'TLV432','Bangkok','Ben Gurion','2013-08-15','2013-08-16');\r\n" + 
			"\r\n" + 
			"/*Table structure for table `flight_seat` */\r\n" + 
			"\r\n" + 
			"DROP TABLE IF EXISTS `flight_seat`;\r\n" + 
			"\r\n" + 
			"CREATE TABLE `flight_seat` (\r\n" + 
			"  `seat_id` int(11) NOT NULL,\r\n" + 
			"  `flight_id` int(11) NOT NULL,\r\n" + 
			"  `class` varchar(10) NOT NULL,\r\n" + 
			"  `row` int(11) NOT NULL,\r\n" + 
			"  `column` char(1) NOT NULL,\r\n" + 
			"  `cost` float NOT NULL,\r\n" + 
			"  `reserved` tinyint(1) NOT NULL,\r\n" + 
			"  PRIMARY KEY (`seat_id`),\r\n" + 
			"  KEY `flt_st_flt` (`flight_id`),\r\n" + 
			"  CONSTRAINT `flt_st_flt` FOREIGN KEY (`flight_id`) REFERENCES `flight` (`flight_id`)\r\n" + 
			") ENGINE=InnoDB DEFAULT CHARSET=latin1;\r\n" + 
			"\r\n" + 
			"/*Data for the table `flight_seat` */\r\n" + 
			"\r\n" + 
			"insert  into `flight_seat`(`seat_id`,`flight_id`,`class`,`row`,`column`,`cost`,`reserved`) values (1,1,'Business',1,'A',400,0),(2,2,'Business',1,'B',550,0),(3,1,'Economy',20,'E',230,0),(4,4,'Economy',22,'A',235,0),(5,5,'Economy',21,'F',190,1),(6,3,'Economy',11,'E',339,0),(7,1,'Business',1,'B',400,0);\r\n" + 
			"\r\n" + 
			"/*Table structure for table `payment_method` */\r\n" + 
			"\r\n" + 
			"DROP TABLE IF EXISTS `payment_method`;\r\n" + 
			"\r\n" + 
			"CREATE TABLE `payment_method` (\r\n" + 
			"  `method_id` int(11) NOT NULL,\r\n" + 
			"  `user_id` int(11) NOT NULL,\r\n" + 
			"  `company` varchar(50) NOT NULL,\r\n" + 
			"  PRIMARY KEY (`method_id`),\r\n" + 
			"  KEY `flt_pmnt_usr_fk` (`user_id`),\r\n" + 
			"  CONSTRAINT `flt_pmnt_usr_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`)\r\n" + 
			") ENGINE=InnoDB DEFAULT CHARSET=latin1;\r\n" + 
			"\r\n" + 
			"/*Data for the table `payment_method` */\r\n" + 
			"\r\n" + 
			"insert  into `payment_method`(`method_id`,`user_id`,`company`) values (1,1,'Visa'),(2,1,'Mastercard'),(3,2,'Cal'),(4,3,'Visa');\r\n" + 
			"\r\n" + 
			"/*Table structure for table `ticket` */\r\n" + 
			"\r\n" + 
			"DROP TABLE IF EXISTS `ticket`;\r\n" + 
			"\r\n" + 
			"CREATE TABLE `ticket` (\r\n" + 
			"  `ticket_id` int(11) NOT NULL,\r\n" + 
			"  `booking_id` int(11) NOT NULL,\r\n" + 
			"  `seat_id` int(11) NOT NULL,\r\n" + 
			"  `conn_prev_ticket_id` int(11) DEFAULT NULL,\r\n" + 
			"  `passport_number` varchar(10) NOT NULL,\r\n" + 
			"  `first_name` varchar(50) NOT NULL,\r\n" + 
			"  `last_name` varchar(50) NOT NULL,\r\n" + 
			"  PRIMARY KEY (`ticket_id`),\r\n" + 
			"  KEY `flt_tkt_bk` (`booking_id`),\r\n" + 
			"  KEY `flt_tkt_st` (`seat_id`),\r\n" + 
			"  KEY `flt_tkt_tkt` (`conn_prev_ticket_id`),\r\n" + 
			"  CONSTRAINT `flt_tkt_bk` FOREIGN KEY (`booking_id`) REFERENCES `booking` (`booking_id`),\r\n" + 
			"  CONSTRAINT `flt_tkt_st` FOREIGN KEY (`seat_id`) REFERENCES `flight_seat` (`seat_id`),\r\n" + 
			"  CONSTRAINT `flt_tkt_tkt` FOREIGN KEY (`conn_prev_ticket_id`) REFERENCES `ticket` (`ticket_id`)\r\n" + 
			") ENGINE=InnoDB DEFAULT CHARSET=latin1;\r\n" + 
			"\r\n" + 
			"/*Data for the table `ticket` */\r\n" + 
			"\r\n" + 
			"insert  into `ticket`(`ticket_id`,`booking_id`,`seat_id`,`conn_prev_ticket_id`,`passport_number`,`first_name`,`last_name`) values (1,1,1,NULL,'047592848','Yizhar','Gilboa'),(2,1,7,NULL,'857361923','Miss','Pots'),(3,2,2,NULL,'192949568','Guy','Lev'),(4,3,6,NULL,'888222463','Amir','Yonatan');\r\n" + 
			"\r\n" + 
			"/*Table structure for table `users` */\r\n" + 
			"\r\n" + 
			"DROP TABLE IF EXISTS `users`;\r\n" + 
			"\r\n" + 
			"CREATE TABLE `users` (\r\n" + 
			"  `user_id` int(11) NOT NULL,\r\n" + 
			"  `user_name` varchar(50) NOT NULL,\r\n" + 
			"  `password` varchar(50) NOT NULL,\r\n" + 
			"  `registration_date` date NOT NULL,\r\n" + 
			"  `first_name` varchar(50) NOT NULL,\r\n" + 
			"  `last_name` varchar(50) NOT NULL,\r\n" + 
			"  `address` varchar(250) NOT NULL,\r\n" + 
			"  `email_address` varchar(250) NOT NULL,\r\n" + 
			"  PRIMARY KEY (`user_id`)\r\n" + 
			") ENGINE=InnoDB DEFAULT CHARSET=latin1;\r\n" + 
			"\r\n" + 
			"/*Data for the table `users` */\r\n" + 
			"\r\n" + 
			"insert  into `users`(`user_id`,`user_name`,`password`,`registration_date`,`first_name`,`last_name`,`address`,`email_address`) values (1,'yizhar','1234','2013-08-04','Yizhar','Gilboa','Doc 20 Tel-Aviv','yizhar.gilboa@gmail.com'),(2,'guy','4321','2013-08-05','Guy','Lev','Prof 30 Tel-Aviv','guylev9@gmail.com'),(3,'amir','1324','2013-08-06','Amir','Yonatan','Mast 10 Tel-Aviv','amir.yonatan1@gmail.com');\r\n" + 
			"\r\n" + 
			"/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;\r\n" + 
			"/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;\r\n" + 
			"/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;\r\n" + 
			"/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;\r\n" + 
			"";
	
	public static void Init() throws SQLException, IOException
	{
		Connection conn = DBConn.getConnection();
		Statement stmt = conn.createStatement();
		//stmt.execute(FLIGHTS_DB);
		DBSqlScript dbs = new DBSqlScript(conn, false, true);
		dbs.runScript(new BufferedReader(new StringReader(FLIGHTS_DB)));
	}
}
